﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Entity;
using Command;
using System.Data.SqlClient;
namespace DAL
{
    public class Z_Book
    {
        //查询主页所有书籍
        public static List<BookInformatica> GetBooks()
        {
            string sql = "select *,(select TypeName from BookType where TypeID=b.TypeID) 'TypeName' from BookInformatica b";
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<BookInformatica> Books = new List<BookInformatica>();
            while (sdr.Read())
            {
                BookInformatica b = new BookInformatica();
                b.BookId = (int)sdr["BookId"];
                b.BookName = (string)sdr["BookName"];
                b.BookPress = (string)sdr["BookPress"];
                b.BookAuthor = (string)sdr["BookAuthor"];
                b.BookDescribe = (string)sdr["BookDescribe"];
                b.BookPicture = (string)sdr["BookPicture"];
                b.BookPoster = sdr["BookPoster"].ToString();
                b.BookDetails = sdr["BookDetails"].ToString();
                b.BookPrice = (decimal)sdr["BookPrice"];
                b.TypeID = (int)sdr["TypeID"];
                //b.TypeName = (string)sdr["TypeName"];
                b.BookNumber = sdr["BookNumber"].ToString();
                b.BookState = (int)sdr["BookState"];
                b.BookTime = (DateTime)sdr["BookTime"];
                Books.Add(b);
            }
            sdr.Close();
            return Books;
        }
        //根据ID查看详情
        public static List<BookInformatica> GetDetail(int id) 
        {
            string sql = string.Format("select *,(select TypeName from BookType where TypeID=b.TypeID) 'TypeName' from BookInformatica b where b.BookID={0}", id);
            SqlDataReader sdr = DBHelper.Getdata(sql);
            
            List<BookInformatica> books = new List<BookInformatica>();
            while (sdr.Read())
            {
                BookInformatica o = new BookInformatica();
                
                o.BookId = (int)sdr["BookId"];
                o.BookName = (string)sdr["BookName"];
                o.BookPress = (string)sdr["BookPress"];
                o.BookAuthor = (string)sdr["BookAuthor"];
                o.BookDescribe = (string)sdr["BookDescribe"];
                o.BookPicture = (string)sdr["BookPicture"];
                o.BookPoster = sdr["BookPoster"].ToString();
                o.BookDetails = sdr["BookDetails"].ToString();
                o.BookTime = (DateTime)sdr["BookTime"];
                o.BookPrice = (decimal)sdr["BookPrice"];
                o.TypeID = (int)sdr["TypeID"];
                o.BookNumber = sdr["BookNumber"].ToString();
                o.BookState = (int)sdr["BookState"];
                o.TypeName = (string)sdr["TypeName"];
                books.Add(o);
               
            }
            sdr.Close();
            return books;
        }
        //模糊查询   首页查询图书功能
        public static List<BookInformatica> Getmohu(string name)
        {
            string sql = string.Format("select * from BookInformatica where BookName like '%{0}%'", name);
       
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<BookInformatica> Books = new List<BookInformatica>();
            while (sdr.Read())
            {
                BookInformatica b = new BookInformatica();
                b.BookId = (int)sdr["BookId"];
                b.BookName = (string)sdr["BookName"];
                b.BookPress = (string)sdr["BookPress"];
                b.BookAuthor = (string)sdr["BookAuthor"];
                b.BookDescribe = (string)sdr["BookDescribe"];
                b.BookPicture = (string)sdr["BookPicture"];
                b.BookPoster = sdr["BookPoster"].ToString();
                b.BookDetails = sdr["BookDetails"].ToString();
                b.BookPrice = (decimal)sdr["BookPrice"];
                b.TypeID = (int)sdr["TypeID"];
                //b.TypeName = (string)sdr["TypeName"];
                b.BookNumber = sdr["BookNumber"].ToString();
                b.BookState = (int)sdr["BookState"];
                b.BookTime = (DateTime)sdr["BookTime"];
                Books.Add(b);
            }
            sdr.Close();
            return Books;
        }
        //菜单动态显示
        public static List<BookType> GetCaiDan()
        {
            string sql = "select * from BookType where FaTherId=0";
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<BookType> Types = new List<BookType>();
            while (sdr.Read()) {
                BookType t = new BookType();
                t.TypeID = (int)sdr["TypeID"];
                t.TypeName = (string)sdr["TypeName"];
                t.FatherId = (int)sdr["FatherId"];
                //t.Note = (string)sdr["Note"];
                t.state = (int)sdr["state"];
                t.Children =GetCaiDant(t.TypeID);
                Types.Add(t);

            }
            sdr.Close();
            return Types;
        }
        //二级菜单动态显示
        public static List<BookType> GetCaiDant(int id)
        {
            string sql = string.Format("select * from BookType where FaTherId='{0}'", id);
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<BookType> Types = new List<BookType>();
            while (sdr.Read())
            {
                BookType t = new BookType();
                t.TypeID = (int)sdr["TypeID"];
                t.TypeName = (string)sdr["TypeName"];
                t.FatherId = (int)sdr["FatherId"];
                //t.Note = (string)sdr["Note"];
                //t.state = (int)sdr["state"];
                Types.Add(t);

            }
            sdr.Close();
            return Types;
        }
        //根据图书类别查询图书
        public static List<BookInformatica> Getfn(int typeid)
        {
            string sql = string.Format("select *from BookInformatica where TypeID={0} ", typeid);
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<BookInformatica> books = new List<BookInformatica>();
            while (sdr.Read())
            {
                BookInformatica p = new BookInformatica();
                p.BookId = (int)sdr["BookId"];
                p.BookPicture=(string)sdr["BookPicture"];
                p.BookName = (string)sdr["BookName"];
                p.TypeID = (int)sdr["TypeID"];
                p.BookAuthor = (string)sdr["BookAuthor"];
                p.BookPrice = (decimal)sdr["BookPrice"];
                p.BookDescribe = (string)sdr["BookDescribe"];
                books.Add(p);
            }
            sdr.Close();
            return books;
        }
        //根据图书Id显示图书评论
        public static List<Comment> Getcomment(int id)
        {
            string sql = string.Format("select * from Comment where BookId={0}", id);
            SqlDataReader sdr = DBHelper.Getdata(sql);
            List<Comment> coms = new List<Comment>();
            while (sdr.Read()) 
            {
                Comment c = new Comment();
                c.BookId = (int)sdr["BookId"];
                c.CommentId = (int)sdr["CommentId"];
                c.CommentState = (int)sdr["CommentState"];
                c.CommentValue = (string)sdr["CommentValue"];
                c.UserId = (int)sdr["UserId"];
                c.CommentDate = (DateTime)sdr["CommentDate"];
                coms.Add(c);
            }
            sdr.Close();
            return coms;
        }
    }
}
